package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;

import com.eduction.modules.system.entity.CourseAnalyseMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.common.dbutil.DbHelper;
import com.eduction.modules.system.entity.InterviewAnalyseMap;

@Repository
public class InterviewAnalyseMapRepository {
	@Autowired
	private DbHelper dbhelper;
	
	@Autowired
	private InterviewAnalyseMaoDao interviewAnalyseMaoDao;
	
	/**
	 * 添加一张新表
	 */
	public void saveInterviewAnalyseMap(InterviewAnalyseMap c){
		this.interviewAnalyseMaoDao.save(c);
	}
	
	/**
	 * 修改表的填报状态
	 */
	public void updateInterviewAnalyseMapState(String sign,String schoolId,String expertId) throws SQLException{
		String sql = "update t_interview_analyse_map set s_state=1 where s_sign = ? and s_school_id = ? and s_expert_id = ?";
		this.dbhelper.execute(sql, sign,schoolId,expertId);
	}
	
	/**
	 * 查询这个专家下的所有表
	 * @throws SQLException 
	 */
	public List<InterviewAnalyseMap> queryMapsByExpert(String expertId,String identity) throws SQLException{
		if("3".equals(identity)){
			String sql = "select a.p_id as pid,a.s_expert_id as expertId,a.s_sign as sign,s.s_name as schoolName," +
					"a.s_school_id as schoolId,a.s_state as state from " +
					"t_interview_analyse_map as a left join t_school as s on a.s_school_id = s.p_id where a.s_expert_id in " +
					"(SELECT ei.s_expert_id from t_expert_group_map ei WHERE s_group_id in " +
					"(SELECT egm.s_group_id from t_expert_group_map egm  left JOIN t_group g ON g.p_id=egm.s_group_id " +
					"where egm.s_expert_id=? AND g.s_state=1))";
			return this.dbhelper.getBeanList(sql, InterviewAnalyseMap.class, expertId);
		}else{
			String sql = "select a.p_id as pid,a.s_expert_id as expertId,a.s_sign as sign,s.s_name as schoolName,a.s_school_id as schoolId,a.s_state as state from t_interview_analyse_map as a left join t_school as s on a.s_school_id = s.p_id where a.s_expert_id = ?";
			return this.dbhelper.getBeanList(sql, InterviewAnalyseMap.class, expertId);
		}
	}

    public InterviewAnalyseMap queryIsExist(String sign, String schoolId, String expertId)  throws SQLException {
		String sql = "select a.* from t_interview_analyse_map as a where a.s_sign=? and s_school_id=? and s_expert_id=?  ";
		return this.dbhelper.getBean(sql, InterviewAnalyseMap.class, sign, schoolId, expertId);
	}

	public InterviewAnalyseMap findByPid(String pid) {
		return interviewAnalyseMaoDao.findOne(pid);
	}

	public void deleteInterviewAnalyseMapState(String sign, String schoolId, String expertId) throws SQLException {
		String sql = "delete from t_course_analyse where sign =? and school_id=? and expert_id=? ";
		this.dbhelper.execute(sql,sign, schoolId, expertId);
	}

	public void deleteByPid(String pid) {
		interviewAnalyseMaoDao.delete(pid);
	}

    public List<InterviewAnalyseMap> queryMapBySchoolId(String schoolId)  throws SQLException {
		String sql = "select a.p_id as pid,a.s_expert_id as expertId,a.s_sign as sign,s.s_name as schoolName," +
				"a.s_school_id as schoolId,a.s_state as state from " +
				"t_interview_analyse_map as a left join t_school as s on a.s_school_id = s.p_id where a.s_expert_id in " +
				"(SELECT ei.s_expert_id from t_expert_group_map ei WHERE s_group_id in " +
				"(SELECT egm.s_group_id from t_expert_group_map egm  left JOIN t_group g ON g.p_id=egm.s_group_id " +
				"where g.s_state=1)) AND a.s_school_id=? ";
		return this.dbhelper.getBeanList(sql, InterviewAnalyseMap.class, schoolId);
    }
}
